package com.gitee.melin.bee.core.jdbc.dialect.oracle;

import com.gitee.melin.bee.core.jdbc.dialect.AbstractDBQuery;

public class OracleQuery extends AbstractDBQuery {

    @Override
    public String tablesSql(String schemaName) {
        return "SELECT A.*, C.DATA_LENGTH FROM ALL_TAB_COMMENTS A LEFT JOIN (select segment_name  TABLE_NAME,sum(bytes) DATA_LENGTH " +
                "from user_segments group by segment_name) c ON C.TABLE_NAME = A.TABLE_NAME  WHERE A.OWNER='" + schemaName + "'";
    }

    @Override
    public String columnsSql(String schemaName, String tableName) {
        return " SELECT\n" +
                "  A.COLUMN_NAME,\n" +
                " A.DATA_TYPE,\n" +
                "  A.DATA_PRECISION NUMERIC_PRECISION,\n" +
                "  A.DATA_SCALE NUMERIC_SCALE,\n" +
                "  A.CHAR_LENGTH,\n" +
                "  B.COMMENTS,\n" +
                "  A.NULLABLE,\n" +
                "  PF.COLUMN_POSITION KEY_ORDER,\n" +
                " DECODE(PF.COLUMN_NAME2,null,PF.UNIQUENESS,'PRI') key\n" +
                " FROM\n" +
                "  ALL_TAB_COLUMNS A\n" +
                "  INNER JOIN ALL_COL_COMMENTS B ON A.TABLE_NAME = B.TABLE_NAME \n" +
                "  AND A.COLUMN_NAME = B.COLUMN_NAME \n" +
                "  left join  \n" +
                "  (SELECT\n" +
                " F.COLUMN_NAME,\n" +
                "  F.UNIQUENESS,\n" +
                " F.COLUMN_POSITION,\n" +
                " P.COLUMN_NAME COLUMN_NAME2\n" +
                "FROM\n" +
                " (\n" +
                "SELECT\n" +
                " T.COLUMN_NAME,\n" +
                " T.COLUMN_POSITION,\n" +
                " I.INDEX_TYPE,\n" +
                " I.UNIQUENESS \n" +
                "FROM\n" +
                " ALL_IND_COLUMNS T,\n" +
                " all_indexes I \n" +
                "WHERE\n" +
                " T.INDEX_NAME = I.INDEX_NAME \n" +
                " AND T.TABLE_NAME = I.TABLE_NAME\n" +
                " AND T.INDEX_OWNER = '"+ schemaName + "' \n" +
                " AND T.TABLE_NAME = '"+ tableName + "' \n" +
                " ) F left join (\n" +
                "SELECT\n" +
                "  cu.position,\n" +
                "  cu.COLUMN_NAME \n" +
                " FROM\n" +
                "  ALL_CONS_COLUMNS cu,\n" +
                "  ALL_CONSTRAINTS au \n" +
                " WHERE\n" +
                "  cu.constraint_name = au.constraint_name \n" +
                "  AND au.constraint_type = 'P' \n" +
                "  AND cu.OWNER = '"+ schemaName + "' \n" +
                "  AND au.table_name = '"+ tableName + "' \n" +
                ") P ON P.COLUMN_NAME = F.COLUMN_NAME) PF on PF.COLUMN_NAME = A.COLUMN_NAME\n" +
                " WHERE\n" +
                "  A.OWNER = '"+ schemaName + "' \n" +
                "  AND A.TABLE_NAME = '"+ tableName + "' \n" +
                " ORDER BY\n" +
                " A.COLUMN_ID";
    }

    @Override
    public String schemaName() {
        return "OWNER";
    }

    @Override
    public String tableName() {
        return "TABLE_NAME";
    }

    @Override
    public String tableComment() {
        return "COMMENTS";
    }

    @Override
    public String tableType() {
        return "TABLE_TYPE";
    }

    @Override
    public String columnName() {
        return "COLUMN_NAME";
    }

    @Override
    public String columnType() {
        return "DATA_TYPE";
    }

    @Override
    public String columnLength() {
        return "CHAR_LENGTH";
    }

    @Override
    public String columnComment() {
        return "COMMENTS";
    }

    @Override
    public String columnKey() {
        return "KEY";
    }

    @Override
    public String isNullable() {
        return "NULLABLE";
    }

    @Override
    public String nullableValue() {
        return "Y";
    }
}
